import copy
import io
import json
import datetime
import pickle
from typing import Union

from openpyxl import load_workbook

from apis import api_routes
from flask import abort, g, request, jsonify
from security.decoration import certification
from sqlalchemy import text
from sqlalchemy import desc
from security.security import decode_jwt
from models.facility.facility import Facility
from models.facility.upkeep import Upkeep as Uk
from public.contexts import context_logs


@api_routes.route("/facility", methods=["POST"])
@certification(auth=True, role=None)
def func_facility_post_api():
    """
    添加新设备, 完成API开发
    Content-Type=application/json
    methods=["POST"]
    {
    "workplace"  string     ****    设备存放地址
    "serials"    string     ****    设备编号NO
    "name"       string     ****    设备名字
    "station"    string     ****    工站名称
    "time"       string     ****    设备添购日期      格式 YYYY-MM-DD
    "expired"    string     ****    设备有效截至日期   格式 YYYY-MM-DD
    }
    :return:
        {'status': 400, 'msg': '客户端发送请求异常'}
        {'status': 4000, 'msg': "设备编号已使用"}
        {'status': 200, 'msg': "添加设备成功", }
    ---
    tags:
      - 设备管理
    """
    request_json = request.get_json()
    workplace = request_json.get("workplace")
    serials = request_json.get("serials")
    name = request_json.get("name")
    time = request_json.get("time")
    expired = request_json.get("expired")
    station = request_json.get("station")

    if not all([workplace, serials, name, station, time, expired]):
        abort(400)

    facility: Union[Facility, None] = g.session.query(Facility).filter(Facility.serials == serials).first()
    if facility is not None:
        return jsonify({'status': 4000, 'msg': "设备编号已使用"})

    user_id: int = decode_jwt(g.token)["user_id"]

    time1 = datetime.datetime.strptime(time, "%Y-%m-%d")
    expired1 = datetime.datetime.strptime(expired, "%Y-%m-%d")

    g.session.add(Facility(
        workplace=workplace, serials=serials, name=name,
        station=station, user_id=user_id, time=time1, expired=expired1
    ))

    context_logs(name="添加设备", obsolescence=None, data=json.dumps(request_json))
    g.session.commit()

    return jsonify({'status': 200, 'msg': "添加设备成功"})


@api_routes.route("/facility", methods=["GET"])
@certification(auth=True, role=None)
def func_facility_get_api():
    """
    查询设备, 完成API开发
    http://127.0.0.1:8080/api/facility
    Content-Type=application/x-www-form-urlencoded
    methods=["POST"]
    {
    "index"     int      ****  第几页，默认为1
    "size"      int      ****  每页数据多少, 默认每页10条数据
    }
    :return:
        {'status': 200, 'msg': "添加账户成功", }
    ---
    tags:
      - 设备管理
    """
    index = request.args.get("index", default=1, type=int)
    size = request.args.get("size", default=10, type=int)

    sql_count = """
        SELECT 
            COALESCE(COUNT(facility.id), 0)
        FROM facility
    """
    sql_data = """
        SELECT 
            facility.id, facility.workplace, facility.serials,
            facility.name, facility.station, coalesce(to_char(facility.time,'YYYY-MM-DD'), null),
            coalesce(to_char(facility.expired,'YYYY-MM-DD'), null), facility.uuid4, users.name
        FROM facility INNER JOIN users ON facility.user_id = users.id ORDER BY facility.id LIMIT :arg1 OFFSET :arg2
    """
    count: int = g.session.execute(text(sql_count)).fetchone()[0]
    res, page = [], int(count / size) if count / size == int(count / size) else int(count / size) + 1

    data: list[tuple] = g.session.execute(text(sql_data), {"arg1": size, "arg2": (index - 1) * size}).fetchall()
    for index, itr in enumerate(data):
        uuid4key: bool = True if itr[7] is not None else False
        status: Union[str, None] = "设备没对接PLC"
        if uuid4key is True:
            uuid4value = g.cache.get(str(itr[7]))
            if uuid4value is not None:
                status = uuid4value
            else:
                status = "连接PLC超时"
        uk: Uk = g.session.query(Uk).filter(Uk.facility_id == itr[0]).order_by(desc(Uk.id)).limit(1).first()
        repair = uk.remind.strftime("%Y-%m-%d") if uk is not None else None

        res.append({
            "facility_id": itr[0], "workplace": itr[1],
            "serials": itr[2], "name": itr[3], "station": itr[4],
            "time": itr[5], "expired": itr[6], "status": status, "user_name": itr[8], "repair": repair
        })
    doc = {
        "facility_id": "设备ID", "workplace": "设备位置",
        "serials": "设备编号", "name": "设备名字", "station": "设备工站", "time": "设备购买日期",
        "expired": "设备报废日期", "status": "设备状态", "user_name": "创建设备用户", "repair": "下次保养时间"
    }
    return jsonify({'status': 200, 'data': res, "index": index, "size": size, "page": page, "count": count, "doc": doc})


@api_routes.route("/facility", methods=["PUT"])
@certification(auth=True, role=None)
def func_facility_put_api():
    """
    修改设备信息 完成API开发
    Content-Type=application/json
    methods=["POST"]
    {
    "facility_id" int       ***  设备数据库ID
    "workplace"   string         设备存放地址
    "serials"     string         设备编号NO
    "name"        string         设备名字
    "station"     string         工站名称
    "time"        string         设备添购日期      格式 YYYY-MM-DD
    "expired"     string         设备有效截至日期   格式 YYYY-MM-DD
    }
    :return:
        {'status': 4000, 'msg': "没有查询到要更改设备"}
        {'status': 4001, 'msg': "设备序列号已存在，无法更新"}
        {'status': 200, 'msg': "修改设备成功"}
    ---
    tags:
      - 设备管理
    """
    request_json = request.get_json()
    facility_id = request_json.get("facility_id")
    workplace = request_json.get("workplace")
    serials = request_json.get("serials")
    name = request_json.get("name")
    station = request_json.get("station")
    time = request_json.get("time")
    expired = request_json.get("expired")

    if facility_id is None:
        abort(400)
    facility: Union[Facility, None] = g.session.query(Facility).filter(Facility.id == facility_id).first()
    if facility is None:
        return jsonify({'status': 4000, 'msg': "没有查询到要更改设备"})

    context_logs(name="更新设备信息", obsolescence=facility.__repr__(), data=json.dumps(request_json))

    if serials is not None and facility.serials != serials:
        facility1: Union[Facility, None] = g.session.query(Facility).filter(Facility.serials == serials).first()
        if facility1 is not None:
            return jsonify({'status': 4001, 'msg': "设备序列号已存在，无法更新"})
        facility.serials = serials

    if time is not None:
        date_type0 = datetime.datetime.strptime(time, "%Y-%m-%d")
        if date_type0 != facility.time:
            facility.time = date_type0
    if expired is not None:
        date_type1 = datetime.datetime.strptime(expired, "%Y-%m-%d")
        if date_type1 != facility.expired:
            facility.expired = date_type1
    facility.name = name if (name is not None and name != facility.name) else facility.name
    facility.station = station if (station is not None and station != facility.station) else facility.station
    facility.workplace = workplace if (workplace is not None and workplace != facility.workplace) else facility.workplace

    g.session.commit()
    return jsonify({'status': 200, 'msg': "修改设备成功"})


@api_routes.route("/facility", methods=["DELETE"])
@certification(auth=True, role=None)
def func_facility_delete_api():
    """
    删除设备, 完成API开发
    Content-Type=application/json
    methods=["POST"]
    {
    "facility_id"      int     ****    设备ID
    }
    :return:
        {'status': 4000, 'msg': "没有查询到要删除设备"}
        {'status': 4001, 'msg': "系统自带设备, 禁止删除"}
        {'status': 200, 'msg': "删除设备成功"}
    ---
    tags:
      - 设备管理
    """
    request_json = request.get_json()
    facility_id = request_json.get("facility_id")

    if facility_id is None:
        abort(400)
    facility: Union[Facility, None] = g.session.query(Facility).filter(Facility.id == facility_id).first()
    if facility is None:
        return jsonify({'status': 4000, 'msg': "没有查询到要删除设备"})

    if facility.uuid4 is not None:
        return jsonify({'status': 4001, 'msg': "系统自带设备, 禁止删除"})

    g.session.query(Uk).filter(Uk.facility_id == facility_id).delete()
    g.session.query(Facility).filter(Facility.id == facility_id).delete()

    context_logs(name="删除设备信息", obsolescence=facility.__repr__(), data=json.dumps(request_json))

    g.session.commit()

    return jsonify({'status': 200, 'msg': "删除设备成功"})


@api_routes.route("/facility/alarms", methods=["PUT"])
def func_facility_alarm_put_api():
    """
    EXCEL上传MES, 修改报警点表, 完成API开发
    http://127.0.0.1:8080/api/facility/alarms
    Content-Type=multipart/form-data
    methods=["PUT"]
    {
    "facility_id" int       ****  设备数据库ID
    "alarms"      file      ****  EXCEL文件
    }
    :return:
        {'status': 4001, 'msg': "设备序列号已存在，无法更新"}
        {'status': 200, 'msg': "修改设备成功"}
    ---
    tags:
      - 设备管理
    """
    facility_id = request.form.get("facility_id")
    t_alarms = request.files.get("alarms")

    if facility_id is None or t_alarms is None:
        abort(code=400)
    db_facility: Union[Facility, None] = g.session.query(Facility).filter(Facility.id == facility_id).first()
    if db_facility is None:
        return jsonify({'status': 4000, 'msg': "未查询设备"})
    filename = t_alarms.filename
    alarm_data: bytes = t_alarms.read()

    if filename.split(".")[-1].upper() not in ["XLSX"]:
        return jsonify({'status': 4001, 'msg': "文件格式错误"})

    t_parm: list = [
        {
            "ROW": 7,
            "COLUMN": 6,
            "TITLE": "轴报警"
        },
        # #############################################
        {
            "ROW": 7,
            "COLUMN": 7,
            "TITLE": "轴限位报警"
        },
        {
            "ROW": 7,
            "COLUMN": 8,
            "TITLE": "轴限位报警"
        },
        # #############################################
        {
            "ROW": 7,
            "COLUMN": 9,
            "TITLE": "轴限位报警"
        },
        {
            "ROW": 7,
            "COLUMN": 10,
            "TITLE": "其他报警"
        },
        {
            "ROW": 7,
            "COLUMN": 11,
            "TITLE": "其他报警"
        },
        {
            "ROW": 7,
            "COLUMN": 12,
            "TITLE": "其他报警"
        },
        {
            "ROW": 7,
            "COLUMN": 13,
            "TITLE": "其他报警"
        },
        {
            "ROW": 7,
            "COLUMN": 14,
            "TITLE": "其他报警"
        },
        {
            "ROW": 7,
            "COLUMN": 15,
            "TITLE": "其他报警"
        },
        # #############################################
        {
            "ROW": 7,
            "COLUMN": 16,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 17,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 18,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 19,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 20,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 21,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 22,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 23,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 24,
            "TITLE": "物料感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 25,
            "TITLE": "物料感应器报警"
        },
        # #############################################
        {
            "ROW": 7,
            "COLUMN": 26,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 27,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 28,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 29,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 30,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 31,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 32,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 33,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 34,
            "TITLE": "气缸感应器报警"
        },
        {
            "ROW": 7,
            "COLUMN": 35,
            "TITLE": "气缸感应器报警"
        },
    ]

    warnings: dict = {
        "Status": {
            "Key": "DM8040",
            "Option": {
                0: "通讯异常", 1: "未运行", 2: "初始化中", 4: "初始化完成", 8: "自动运行中", 16: "报警中"
            }
        },
        "Alarm": []
    }
    stream_xlsx = io.BytesIO(alarm_data)
    workbook = load_workbook(stream_xlsx)
    sheet = workbook['报警信息']
    temp_list: list = []
    for f_index, f_value in enumerate(t_parm):
        temp_dict: dict = {
            "Key": f_value["TITLE"],
            "Io": sheet.cell(row=f_value["ROW"], column=f_value["COLUMN"]).value,
            "Option": {}
        }
        for index in range(16):
            error: Union[str, None] = sheet.cell(row=f_value["ROW"] + index + 1, column=f_value["COLUMN"]).value
            if error is not None and len(error) > 0:
                temp_dict["Option"][2 ** index] = error
        if len(temp_dict.get("Option")) != 0:
            temp_dict["Io"] = str(temp_dict["Io"])
            temp_list.append(copy.deepcopy(temp_dict))
    warnings["Alarm"] = temp_list

    # for xxx in temp_list:
    #     print(xxx)

    if sheet.cell(row=7, column=46).value is not None:
        warnings["Status"]["Key"] = str(sheet.cell(row=7, column=46).value)

    db_facility.alarms = pickle.dumps(warnings)
    g.session.commit()
    cache_update_key = "update-{}".format(str(db_facility.uuid4))
    g.cache.set(cache_update_key, 1)

    return jsonify({'status': 200, 'msg': "修改设备成功"})
